package com.jxd.petMgr.dao.impl;

import com.jxd.petMgr.dao.IAccountDao;
import com.jxd.petMgr.model.Account;
import com.jxd.petMgr.util.PageUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author lzh
 * @version 1.0
 * @Description
 * @date 2021/11/3
 */


public class AccountDaoImpl extends BaseDao implements IAccountDao {
    /**
     * 插入账户对象，返回是否插入成功
     *
     * @param account 账户对象
     * @return 是否插入成功
     */
    @Override
    public boolean insertAccount(Account account, Connection conn, PreparedStatement pstm) throws SQLException {
        boolean result;
        String sql = "insert into account(deal_type,pet_id,seller_id,buyer_id,price,deal_time) values(?,?,?,?,?," +
                "(select current_date from dual))";
        pstm = conn.prepareStatement(sql);
        pstm.setInt(1, account.getDeal_type());
        pstm.setInt(2, account.getPet_id());
        pstm.setInt(3, account.getSeller_id());
        pstm.setInt(4, account.getBuyer_id());
        pstm.setDouble(5, account.getPrice());
        result = pstm.executeUpdate() > 0;
        return result;
    }

    /**
     * 获取所有交易记录中出现过的宠物id
     *
     * @return 所有交易记录
     */
    @Override
    public List<Integer> getAllPetInAccount() {
        conn = getConn();
        List<Integer> list = new ArrayList<>();
        Integer id = 0;
        String sql = "select pet_id from account";
        try {
            pstm = conn.prepareStatement(sql);
            rs = pstm.executeQuery();
            while (rs.next()) {
                id = rs.getInt("pet_id");
                list.add(id);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }

        return list;
    }

    /**
     * 根据卖家id获取账单
     *
     * @param seller_id 卖家id
     * @return 账单集合
     */
    @Override
    public List<Account> getAccountBySeller_id(int seller_id) {
        conn = getConn();
        List<Account> list = new ArrayList<>();
        String sql = "select id, deal_type,pet_id,seller_id,buyer_id,price,deal_time from account where seller_id = ?";
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, seller_id);
            rs = pstm.executeQuery();
            while (rs.next()) {
                Account account = new Account(rs.getInt("id"), rs.getInt("deal_type"), rs.getInt("pet_id")
                        , rs.getInt("seller_id"), rs.getInt("buyer_id"), rs.getDouble("price")
                        , rs.getString("deal_time"));
                list.add(account);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }
        return list;
    }

    /**
     * 根据买家家id获取账单
     *
     * @param buyer_id 买家id
     * @return 账单集合
     */
    @Override
    public List<Account> getAccountByBuyer_id(int buyer_id) {
        conn = getConn();
        List<Account> list = new ArrayList<>();
        String sql = "select deal_type,pet_id,seller_id,buyer_id,price,deal_time from account where buyer_id = ?";
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, buyer_id);
            rs = pstm.executeQuery();
            while (rs.next()) {
                Account account = new Account(rs.getInt("id"), rs.getInt("deal_type"), rs.getInt("pet_id")
                        , rs.getInt("seller_id"), rs.getInt("buyer_id"), rs.getDouble("price")
                        , rs.getString("deal_time"));
                list.add(account);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }
        return list;
    }

    /**
     * 根据买家id获得账单数目
     *
     * @param id 买家id
     * @return 账单数目
     */
    @Override
    public int getAccountByPetOwnerIDCount(int id) {
        conn = getConn();
        int count = 0;//数量
        String sql = "select count(id) from account where buyer_id = ? or seller_id = ?";
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, id);
            pstm.setInt(2, id);
            rs = pstm.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }
        return count;
    }

    /**
     * 根据买家家id获取账单
     *
     * @param id 买家id
     * @return 账单集合
     */
    @Override
    public List<Account> getAccountByPetOwnerID(PageUtil pageUtil, int id) {
        conn = getConn();
        List<Account> list = new ArrayList<>();
        String sql = "select  id,deal_type,pet_id,seller_id,buyer_id,price,deal_time from account where buyer_id = ? or seller_id = ?" +
                " order by deal_time desc limit ?,? ";
        try {
            pstm = conn.prepareStatement(sql);
            pstm.setInt(1, id);
            pstm.setInt(2, id);
            pstm.setInt(3,pageUtil.getStart());
            pstm.setInt(4,pageUtil.getLimit());
            rs = pstm.executeQuery();
            while (rs.next()) {
                Account account = new Account(rs.getInt("id"), rs.getInt("deal_type"), rs.getInt("pet_id")
                        , rs.getInt("seller_id"), rs.getInt("buyer_id"), rs.getDouble("price")
                        , rs.getString("deal_time"));
                list.add(account);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, pstm, rs);
        }
        return list;
    }


}
